﻿create database Studentinfo

on
(
   name='Studentinfo',
   filename='F:\SQL\Studentinfo.mdf',
   size=5mb,
   maxsize=50mb,
   filegrowth=10%
)
log on
(
   name='Studentinfo_log',
   filename='F:\SQL\Studentinfo_log.ldf',
   size=5mb,
   maxsize=50mb,
   filegrowth=10%
)

use Studentinfo
go
create table StuDent
(
   sTUNO varchar(15),
   stuName nvarchar(20),
   stuAge  int,
   stuAddress nvarchar(50),
   stuSeat int,
   stuSex  nvarchar(1) default('男') check(stuSex='男' or stuSex='女')
)

insert into StuDent(sTUNO,stuName,stuAge,stuAddress,stuSeat,stuSex) 
select 's2501','张秋里',20,'美国硅谷',1,'男' union
select 's2502','李斯文',18,'湖北武汉',2,'女' union
select 's2503','马文才',22,'湖南长沙',3,'男' union
select 's2504','欧阳俊雄',21,'湖北武汉',4,'女' union
select 's2505','梅超风',20,'湖北武汉',5,'男' union
select 's2506','陈旋风',19,'美国硅谷',6,'男' union
select 's2507','陈风',20,'美国硅谷',7,'女' 


create table Score
(
  examNO int,
  stuNO varchar(15),
  writtenExam varchar(200),
  labExam varchar(200)
)

insert into Score(examNO,stuNO,writtenExam,labExam)
select 1,'s2501','50','70' union
select 2,'s2501','60','65' union
select 3,'s2501','86','85' union
select 4,'s2501','40','80' union
select 5,'s2501','70','90' union
select 6,'s2501','85','90' 

--指定别名
select stuNO as 学号, stuName as 姓名 ,stuAddress as 地址,stuSeat as 座位号,stuSex as 性别 from StuDent

--查询 stuName,stuAge,stuAddress
select stuName,stuAge,stuAddress from StuDent

--查询 学号 笔试 机试 指定别名1
select stuNO as 学号 ,writtenExam as 笔试,labExam as 机试 from Score
--指定别名2
select stuNO 学号 ,writtenExam 笔试,labExam 机试 from Score
--指定别名3
select 学号=stuNO,笔试=writtenExam,机试=labExam from Score


select stuNO+stuName+stuAddress+'@'+stuAddress as 邮箱 from StuDent

--查询并计算总分
select stuNO as 学号,writtenExam as 笔试 ,labExam as 机试, writtenExam+labExam as 总分 from Score


select  stuName ,stuAddress from StuDent

select stuAge as 所有年龄 from StuDent

--查询前三
select top 3 * from StuDent 
--查询前四 姓名 座位号
select top 4 stuName,stuSeat from StuDent

select top 50 percent * from StuDent 

select  stuName ,stuAddress='湖北武汉',stuAge=20  from StuDent

--范围查询并降序
select labExam from Score where labExam>=60 and labExam<=80 order by labExam DESC

-- in or
select * from StuDent where stuAddress = '湖北武汉' or stuAddress = '湖南长沙' 
select * from StuDent where stuAddress in('湖北武汉', '湖南长沙')

select writtenExam from Score where writtenExam<70 order by writtenExam

select * from  StuDent where stuAge is  null or stuAge=''
select*from StuDent where stuAge is not null and not stuage='' 

--模糊查询
select * from  StuDent where stuName like'张%'

select * from  StuDent where stuAddress like '%湖%'

select * from  StuDent where stuName like'张_'

select * from  StuDent where stuName like'__俊%'

select * from StuDent order by stuAge DESC

select * from StuDent order by stuAge DESC , stuSeat ASC

select top 1 * from Score order by  writtenExam DESC

select top 1 * from Score order by  labExam ASC